#########################################################-*- coding: utf-8 -*-
#
# 
# db, helpful functions for database operation
#
# PLEASE PAY MAY ATTENTION TO THE DATABASE ENCODING PROBLEM, ALL character_set_%
# SHOULD BE SET TO utf8 AS SERVER ARE CONFIGURED AS utf8, including:
# 
# character_set_client='utf8'
# character_set_connection='utf8'
# character_set_results='utf8'
# 
# 
##############################################################################

import sys
import re
import utils
import mysql                    # module from mypysql

class Stock(object):
    # database tables
    dbname = 'stock'
    table_info = 'info'
    table_info_ext = 'info_ext'
    table_candle = 'candle'
    table_status = 'status'
    # cursor
    cursor = None
    # error code
    error = -1
    error_db = -2
    error_table = -3

    # Basic stock info, almost do not change frequently
    sql_create_info = """
                      CREATE TABLE IF NOT EXISTS `%s`
                      (
                       `id` INT(16) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                       `code` CHAR(8) UNIQUE,
                       `name` VARCHAR(32) UNIQUE,
                       `full_name` VARCHAR(128),
                       `reg_addr` VARCHAR(256),
                       `region` CHAR(8),
                       `province` CHAR(12),
                       `city` VARCHAR(32),
                       `industry_code` CHAR(2),
                       `website` VARCHAR(32),
                       `ipo_date` DATE,
                       `total_share` BIGINT(35) UNSIGNED DEFAULT 0,
                       `flow_share` BIGINT(35) UNSIGNED DEFAULT 0,
                       `type_mask` INT(32) UNSIGNED DEFAULT 0
                      ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
                      """%self.table_info
    # P/E = price / eps(earn per share)
    sql_create_info_ext = """
                      CREATE TABLE IF NOT EXISTS `%s`
                      (
                        `id` INT(16) UNSIGNED,
                        `eps` FLOAT(6, 3) DEFAULT 0.0,
                        `net_asset` FLOAT(8, 4),
                        `price` FLOAT(6, 2) DEFAULT 0.0,
                        `last_date` DATE,
                        CONSTRAINT 'id = info.id'
                      ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
                          """%self.table_info_ext
    sql_create_candle = """
                                CREATE TABLE IF NOT EXISTS `%s`
                                (
                                  `code` CHAR(8),
                                  `day` DATE,
                                  `min` FLOAT(6, 2) DEFAULT 0.0,
                                  `max` FLOAT(6, 2) DEFAULT 0.0,
                                  `open` FLOAT(6, 2) DEFAULT 0.0,
                                  `close` FLOAT(6, 2) DEFAULT 0.0,
                                  `total_hands` FLOAT(10, 1) DEFAULT 0.0,
                                  `total_price` INT(12) UNSIGNED DEFAULT 0
                                ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
                        """
    # We should use info.id as foreign key
    sql_create_status = """
                                CREATE TABLE IF NOT EXISTS `%s`
                                (
                                  `id` CHAR(13) UNIQUE,
                                  `code` CHAR(8),
                                  `year` YEAR,
                                  `status` INT(12) UNSIGNED DEFAULT 0,
                                  `jan_status` INT(32) UNSIGNED DEFAULT 0,
                                  `feb_status` INT(32) UNSIGNED DEFAULT 0,
                                  `mar_status` INT(32) UNSIGNED DEFAULT 0,
                                  `apr_status` INT(32) UNSIGNED DEFAULT 0,
                                  `may_status` INT(32) UNSIGNED DEFAULT 0,
                                  `jun_status` INT(32) UNSIGNED DEFAULT 0,
                                  `jul_status` INT(32) UNSIGNED DEFAULT 0,
                                  `aug_status` INT(32) UNSIGNED DEFAULT 0,
                                  `sep_status` INT(32) UNSIGNED DEFAULT 0,
                                  `oct_status` INT(32) UNSIGNED DEFAULT 0,
                                  `nov_status` INT(32) UNSIGNED DEFAULT 0,
                                  `dec_status` INT(32) UNSIGNED DEFAULT 0
                                ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
                        """
    
    def __init__(self, host, user, passwd, port = 3306, db = 'stock'):
        """ Stock database basic manipulate interface.
            TODO: wrap all method of class cursor into this class so
            to hide the detail of cursor.
        """
        self.dbname = db        
        self._host = host
        self._user = user
        self._passwd = passwd
        self._port = port
        
        # All stocks
        #self.all_stocks = []

        # connect to db and do some initialize
        self.connect()
        self.set_encoding()
        if self.use_db() == False:
            utils.msg('Database "stock" is not exists, creating now...')
            self.create_db()        # ONLY DO THIS IF DB IS NOT EXISTS
            self.use_db()
            self.create_table_info()        # ONLY DO THIS IF DB IS NOT EXISTS
            self.create_table_candle()      # ONLY DO THIS IF DB IS NOT EXISTS
            self.create_table_status()      # ONLY DO THIS IF DB IS NOT EXISTS

        
    def connect(self):
        """ TODO: match exactly Exceptions!
            Connect to database server, and get the `cursor`
        """
        try:
            self.res = mysql.connect(host=self._host, user=self._user,
                                     passwd=self._passwd, port=self._port)
        except:
            utils.error('Failed to connect MySQL server: %s:%s@%s:%s'
                        %(self._user, self._passwd, self._host, self._port))
            return ERROR
        self.cursor = self.res.cursor()

        

    def create_db(self):
        """ Internal helper function: create database stock
        """
        self.sql_create_db = """CREATE DATABASE IF NOT EXISTS `%s`
                                CHARACTER SET 'utf8';
                             """%self.dbname
        self.cursor.execute(self.sql_create_db)

        
    def use_db(self):
        """ If database is not exists, return False so to create
            one and it's tables, else return True and do nothing.
        """
        utils.msg('Opening database "%s"'%self.dbname)
        self.sql_use_db = 'USE `%s`;'%self.dbname
        try:
            self.cursor.execute(self.sql_use_db)
        except:
            return False
        return True

    # set encoding! VERY IMPORTANT!
    def set_encoding(self):
        self.cursor.execute("set character_set_client='utf8';")
        self.cursor.execute("set character_set_connection='utf8';")
        self.cursor.execute("set character_set_results='utf8';")        

    # 
    # create all tables
    #
        
    def create_table_info(self):
        # table `info`
        #utils.msg('Creating table `%s`'%self.table_info)
        try:
            self.cursor.execute("""
                             CREATE TABLE IF NOT EXISTS `%s`
                             (
                               `id` INT(16) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                               `code` CHAR(8) UNIQUE,
                               `name` VARCHAR(32) UNIQUE,
                               `full_name` VARCHAR(128),
                               `reg_addr` VARCHAR(256),
                               `region` CHAR(8),
                               `province` CHAR(12),
                               `city` VARCHAR(32),
                               `industry_code` CHAR(2),
                               `website` VARCHAR(32),
                               `ipo_date` DATE,
                               `last_date` DATE,
                               `total_share` BIGINT(35) UNSIGNED DEFAULT 0,
                               `flow_share` BIGINT(35) UNSIGNED DEFAULT 0,
                               `share_asset` FLOAT(8, 4),
                               `share_earning` FLOAT(8, 4),
                               `pe` FLOAT(6, 2),
                               `type_mask` INT(32) UNSIGNED DEFAULT 0
                             ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
                            """
                            %self.table_info)
        except:
            utils.error('Failed to create table `%s`'%self.table_info)

    def create_table_candle(self):
        # table `candle`
        #utils.msg('Creating table `stock.%s`'%self.table_candle)        
        try:
            self.cursor.execute("""
                                CREATE TABLE IF NOT EXISTS `%s`
                                (
                                  `code` CHAR(8),
                                  `day` DATE,
                                  `min` FLOAT(6, 2) DEFAULT 0.0,
                                  `max` FLOAT(6, 2) DEFAULT 0.0,
                                  `open` FLOAT(6, 2) DEFAULT 0.0,
                                  `close` FLOAT(6, 2) DEFAULT 0.0,
                                  `total_hands` FLOAT(10, 1) DEFAULT 0.0,
                                  `total_price` INT(12) UNSIGNED DEFAULT 0
                                ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
                                """
                                %self.table_candle)
        except:
            utils.error('Failed to create table `%s`'%self.table_candle)

    def create_table_status(self):
        # table `status`
        #utils.msg('Creating table `%s`'%self.table_status)        
        try:
            self.cursor.execute("""
                                CREATE TABLE IF NOT EXISTS `%s`
                                (
                                  `id` CHAR(13) UNIQUE,
                                  `code` CHAR(8),
                                  `year` YEAR,
                                  `status` INT(12) UNSIGNED DEFAULT 0,
                                  `jan_status` INT(32) UNSIGNED DEFAULT 0,
                                  `feb_status` INT(32) UNSIGNED DEFAULT 0,
                                  `mar_status` INT(32) UNSIGNED DEFAULT 0,
                                  `apr_status` INT(32) UNSIGNED DEFAULT 0,
                                  `may_status` INT(32) UNSIGNED DEFAULT 0,
                                  `jun_status` INT(32) UNSIGNED DEFAULT 0,
                                  `jul_status` INT(32) UNSIGNED DEFAULT 0,
                                  `aug_status` INT(32) UNSIGNED DEFAULT 0,
                                  `sep_status` INT(32) UNSIGNED DEFAULT 0,
                                  `oct_status` INT(32) UNSIGNED DEFAULT 0,
                                  `nov_status` INT(32) UNSIGNED DEFAULT 0,
                                  `dec_status` INT(32) UNSIGNED DEFAULT 0
                                ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
                                """
                                %self.table_status)
        except:
            utils.error('Failed to create table `%s`'%self.table_status)
            

    def create_table_td(self, code):
        # table `td_xxxxxxxxx`
        self._code = code
        #utils.msg('Creating table `td_%s`'%self._code)        
        try:
            self.cursor.execute("""
                                CREATE TABLE IF NOT EXISTS `td_%s`
                                (
                                  `date` DATE,
                                  `time` TIME,
                                  `price` FLOAT(6, 2),
                                  `hands` INT(16) UNSIGNED,
                                  `total_price` INT(32) UNSIGNED,
                                  `attr` CHAR(1)
                                ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
                                """
                                %self._code)
        except:
            utils.error('Fail to create table `td_%s`'%self._code)

    def query_stock_code(self, begin=0, end=0):
        """ Query stock code from stock.info table, if no parameter is given,
            return all queried rows, else return row between 'begin' and 'end'.
        """
        self.sql_query = "select `code` from `%s` "%self.table_info
        if (begin != 0 or end != 0) and begin <= end and begin >= 0:
            self.sql_query += 'limit %d, %d'%(begin, end)
        self.sql_query += ';'
        try:
            self.cursor.execute(self.sql_query)
        except:
            utils.error('Fail to query stock code')
            return ERROR
        
        return self.cursor.fetchall()

    
    def create_per_stock_table(self):
        """ FIXME: Currently, we create all stock tables, and let DB server
            to decide whether it is exists. Later, we should only create
            per stock stable incrementally!
        """
        rows = self.query_stock_code()
        utils.msg('creating %d tables, may take a while...'%len(rows))
        for r in rows:
            self.create_table_td(r)

    def fill_info_table(self, exchange, info_list):
        """ Fill table `info` with info_list, exchange can be
            'sz' and 'sh', according to exchange type, different parse
            logic for info_list is used.
            sz info list format: (with 20 '|' divider)
            '  000001| 深 发 展| 深圳发展银行股份有限公司| SHENZHEN DEVELOPMENT BANK CO.,LTD| 广东省深圳市罗湖区深南东路5047号| 000001| 深发展Ａ| 1991-04-03| 5,123,350,416| 3,102,470,266| | | | 0| 0| 华南| 广东| 深圳市| I 金融保险| www.sdb.com.cn|'
            sh info list format:
            !!TODO!!
        """
        if exchange == 'sz':
            for info in info_list:
                one = re.split('\|', info)
                self.code      = exchange + one[0][-6:] # sz + tailing 6 bit
                self.name      = one[6].replace(' ', '') # remove whitespace
                if self.name == '':                      # B share only
                    continue
                self.full_name = one[2].replace(' ', '')
                self.reg_addr  = one[4].replace(' ', '')
                self.region    = one[-6].replace(' ', '')
                self.province  = one[-5].replace(' ', '')
                self.city      = one[-4].replace(' ', '')
                self.industry  = one[-3].strip()
                self.industry_code = self.industry.split(' ')[0]
                self.website   = one[-2].replace(' ', '').replace('http://', '')
                self.website   = re.sub('(/.*$|;.*$|,.*$)', '', self.website)
                self.ipo_date  = one[7].replace(' ', '').replace('-','')
                self.total_share = one[8].replace(' ', '').replace(',', '')
                self.flow_share = one[9].replace(' ', '').replace(',', '')
                # Execute SQL
                self.sql = """
                           INSERT INTO `%s`
                           (`code`, `name`, `full_name`, `reg_addr`,
                            `region`, `province`, `city`,
                            `industry_code`, `website`, `ipo_date`,
                            `total_share`, `flow_share`)
                            VALUES
                            ('%s', '%s', '%s', '%s',
                             '%s', '%s', '%s',
                             '%s', '%s', %s,
                              %s,   %s);
                           """%(self.table_info, self.code, self.name,
                                self.full_name, self.reg_addr,
                                self.region, self.province, self.city,
                                self.industry_code, self.website,
                                self.ipo_date, self.total_share,
                                self.flow_share)
                try:
                    self.cursor.execute(self.sql)
                except:
                    utils.error("Failed to execute sql: %s"%self.sql)
                    return ERROR
                
            utils.cache_2_file('sz_company.txt', info_list)
            
        elif exchange == 'sh':
            # TODO: we need more info about sh company.
            for info in info_list:
                one = info.split()
                self.code = exchange + one[0]
                self.name = one[1]
                self.sql = """
                           INSERT INTO `%s`
                           (`code`, `name`)
                           VALUES
                           ('%s', '%s');
                           """%(self.table_info, self.code, self.name)
                try:
                    self.cursor.execute(self.sql)
                except:
                    utils.error("Failed to execute sql: %s"%self.sql)
                    return ERROR

            utils.cache_2_file('sh_company.txt', info_list)
                
        else:
            utils.error('unknown exchange type: %s' %exchange)
            return ERROR

        self.all_stocks = []

    def update_info_table(self, exchange, old_info_list, new_info_list):
        """ Update table `stock.info` with info_list, exchange can be
            'sz' and 'sh', according to exchange type, different parse
            logic for info_list is used.
            We stop 2 info_lists, one is last time we used to fill or
            update the info table, second one is that we currently
            downloaded.
        """
        pass

    # Only used as a test function
    def fetch_stock_info(self, code):
        self.cursor.execute("select * from `info` where code='%s'"%code)
        for r in self.cursor.fetchall():
            print('NEXT:', r)

    def fill_raw_data(self, raw_data):
        """ Download per stock time division trading data from internet,
            and fill it into table td_%stock_code. Then update the status
            table.
        """
        pass

    def get_status_column(self, month):
        """ Return the corresponding column of stock.status for given month
        """
        if month == 1:
            return 'jan_status'
        elif month == 2:
            return 'feb_status'
        elif month == 3:
            return 'mar_status'
        elif month == 4:
            return 'apr_status'
        elif month == 5:
            return 'may_status'
        elif month == 6:
            return 'jun_status'
        elif month == 7:
            return 'jul_status'
        elif month == 8:
            return 'aug_status'
        elif month == 9:
            return 'sep_status'
        elif month == 10:
            return 'oct_status'
        elif month == 11:
            return 'nov_status'
        elif month == 12:
            return 'dec_status'
        else:
            utils.error('Unknown month: %d'%month)
            return None
